package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Orders;
import com.edu.bean.Ordersdata;
import com.edu.dao.BaseDao;
import com.edu.dao.OrdersDao;

public class OrdersDaoImpl implements OrdersDao {

	@Override
	public List<Orders> selectAll(int pageNum,int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Orders> orders = new ArrayList();
		String sql = "SELECT * FROM orders ORDER BY order_Id DESC LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				Orders order = new Orders();
				order.setOrder_Id(res.getInt("order_Id"));
				order.setUser_Id(res.getInt("user_Id"));
				order.setCar_Id(res.getInt("car_Id"));
				order.setOrder_date(res.getDate("order_date"));
				order.setPurchase_price(res.getDouble("purchase_price"));
				order.setState(res.getString("state"));
				orders.add(order);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return orders;
		
	}

	@Override
	public int selectAllorder(Orders order) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM orders WHERE 1=1 ";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int deleteOrderById(int orderid) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM orders WHERE order_id = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, orderid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int updateOrders(Orders order) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE orders set user_Id = ?,car_Id = ?,order_date = ?,purchase_price = ?,state= ? WHERE order_Id = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, order.getUser_Id());
			ps.setInt(2, order.getCar_Id());
			ps.setDate(3, new Date(order.getOrder_date().getTime()));
			ps.setDouble(4, order.getPurchase_price());
			ps.setString(5, order.getState() );
			ps.setInt(6, order.getOrder_Id());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public Orders selectOrdersById(int orderid) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Orders order=null;
		String sql = "SELECT * FROM orders WHERE order_Id = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,orderid);
			res = ps.executeQuery();
			if(res.next()) {
				order = new Orders();
				order.setOrder_Id(res.getInt("Order_Id"));
				order.setUser_Id(res.getInt("user_Id"));
				order.setCar_Id(res.getInt("car_Id"));
				order.setOrder_date(res.getDate("order_date"));
				order.setPurchase_price(res.getDouble("purchase_price"));
				order.setState(res.getString("state"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return order;
	}

	@Override
	public int selectpriceAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT SUM(purchase_price) as total FROM orders WHERE state='已付款'";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectonetotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-01' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-01-01' AND '2025-01-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleecttwototal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-02' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-02-01' AND '2025-02-29';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectthreetotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-03' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-03-01' AND '2025-03-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectfourtotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-04' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-04-01' AND '2025-04-30';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectfivetotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-05' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-05-01' AND '2025-05-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectsixtotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-06' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-06-01' AND '2025-06-30';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectseventotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-07' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-07-01' AND '2025-07-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleecteighttotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-08' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-08-01' AND '2025-08-30';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleectninetotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-09' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-09-01' AND '2025-09-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleecttentotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-10' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-10-01' AND '2025-10-30';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleecteleventotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-11' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-11-01' AND '2025-11-30';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int sleecttwelvetotal() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT '2025-12' AS 月份,SUM(purchase_price) AS total FROM orders WHERE state = '已付款' AND order_date BETWEEN '2025-12-01' AND '2025-12-31';";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

}
